﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VswService_V7.ClientCommand;
using VswService_V7.Utility;
using Oracle.ManagedDataAccess.Client;
using System.Reflection;
using System.Globalization;
using VswService_V7.DataBase.Tables.L2INTERFACE;

namespace VswService_V7.DataBase.Tables
{
    public partial class STRIP_INFO
    {
        /// <summary>
        /// 20 items of data per page
        /// </summary>
        public static int pageNum = 20;
        /// <summary>
        /// strip status
        /// </summary>
        public static readonly string created = "0";  // created in database
        public static readonly string scaned = "1";   // strip has been scaned and got the batch_no
        public static readonly string Loaded = "2";   // strip has got the batch_no and loaded
        public static readonly string uncoiled = "3";  //strip load to the uncoiler
        public static readonly string finished = "4";  // strip finished
        public static readonly string abnormal = "-1";
        /// <summary>
        /// Query TASK information and receipe information through stripNo
        /// </summary>
        /// <param name="stripNo"></param>
        /// <returns></returns>
        public static DataTable IsDegalityByStripNo(string stripNo)
        {
            try
            {
                string sql = string.Format("select " +
                    "r.{0},r.{1},r.{2},r.{3} " +
                    "from {4} r " +
                    "join {5} t " +
                    "on r.{6} = t.{7} " +
                    "join {8} s " +
                    "on t.{9} = s.{10} " +
                    "where s.{11} = '{12}' and s.{13} > '{14}'",
                    RECIPE_INFO.fd_recipeNo, RECIPE_INFO.fd_yeildStrength, RECIPE_INFO.fd_accPrPressure_1, RECIPE_INFO.fd_accPrPressure_2,
                    RECIPE_INFO.tableName,
                    TASK_INFO.tableName,
                    RECIPE_INFO.fd_recipeNo, TASK_INFO.fd_recipeNo,
                    tableName,
                    TASK_INFO.fd_taskNo, fd_taskNo,
                    fd_stripNo, stripNo, fd_status, scaned);

                //string sql = string.Format("select * from {0} a, {1} b,{2} c where a.{3}=b.{4} and a.{5}='{6}' and a.{7}='2' and c.{8}=b.{9}",
                //    tableName, TASK_INFO.tableName, RECIPE_INFO.tableName, fd_taskNo, TASK_INFO.fd_taskNo, fd_stripNo, stripNo, fd_status, RECIPE_INFO.fd_recipeNo, TASK_INFO.fd_recipeNo);

                DataTable dataTable = OracleHelper.Exct_QueryTable(sql);
                if (dataTable != null && dataTable.Rows.Count > 0)
                {
                    return dataTable;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("IsDegalityByStripNo", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Find TaskNo by stripNo
        /// </summary>
        /// <param name="stripNo"></param>
        /// <returns></returns>
        public static string Select_TaskNoByStripNo(string stripNo)
        {
            try
            {
                string sql = string.Format("select {0} from {1} where {2}='{3}'",
                     fd_taskNo, tableName, fd_stripNo, stripNo);
                object taskNo = OracleHelper.Exct_ScalarObj(sql);
                if (taskNo != null)
                    return taskNo.ToString();
                else
                    return "0";
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Select_TaskNoByStripNo", ex);
                return "0";
            }
        }


        //public static DataTable Select_TaskByStripNo(string stripNo)
        //{
        //    try
        //    {

        //        string sql = string.Format("select * from {0} where {1}='{2}'",
        //             tableName, fd_stripNo, stripNo);
        //        DataTable stripTable = OracleHelper.Exct_QueryTable(sql);
        //        if (stripTable != null)
        //            return stripTable;
        //        else
        //            return null;
        //    }
        //    catch (Exception ex)
        //    {
        //        LogHelper.WriteLog("Select_TaskNoByStripNo", ex);
        //        return null;
        //    }
        //}



        /// <summary>
        /// Query StripInfo information via taskNo
        /// </summary>
        /// <param name="task_No"></param>
        /// <returns></returns>
        public static DataTable Select_STRIP_INFOByTaskNo(string task_No, int minRow, int maxRow, out string stripTotalPages)
        {
            try
            {
                string sqlstrip = string.Format("select * from (select rownum rn,a.* from (select * from {0} where {1}='{2}' order by {3} asc,{4} desc) a) where rn>{5} and rn<={6}",
                    tableName, fd_taskNo, task_No, fd_status, fd_updateTime, minRow, maxRow);
                string sqlStripcount = string.Format("select count(*) from {0} where {1}='{2}'",
                  tableName, fd_taskNo, task_No);
                DataTable stripTable = OracleHelper.Exct_QueryTable(sqlstrip);
                if (stripTable != null)
                {
                    int stripPages = Convert.ToInt32(TASK_INFO.GetTableCount(sqlStripcount));
                    if (stripPages % pageNum == 0)
                    {
                        stripTotalPages = (stripPages / pageNum).ToString();
                    }
                    else
                    {
                        stripTotalPages = (stripPages / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("Select_STRIP_INFOByTaskNo Success");
                    return stripTable;
                }
                else
                {
                    stripTotalPages = "";
                    return null; ;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Select_STRIP_INFOByTaskNo", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Add strip information
        /// </summary>
        /// <param name="stripTable"></param>
        /// <param name="userNo"></param>
        /// <returns></returns>
        public static DataTable Insert_STRIP_INFO(DataTable stripTable, string userNo, out string totalPages)
        {
            try
            {
                long eventNo = OracleHelper.GetNextEventNo();
                string sqlSTRIP = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}')",
                    tableName, eventNo, stripTable.Rows[0][fd_stripNo].ToString(), stripTable.Rows[0][fd_batchNoE].ToString(), stripTable.Rows[0][fd_batchNoO].ToString(), stripTable.Rows[0][fd_taskNo].ToString(),
                    stripTable.Rows[0][fd_isSpy].ToString(), stripTable.Rows[0][fd_isEx].ToString(), stripTable.Rows[0][fd_stripDurabilityClass].ToString(), stripTable.Rows[0][fd_stripSteelGrade].ToString(),
                    stripTable.Rows[0][fd_stripHeat].ToString(), stripTable.Rows[0][fd_stripThk].ToString(), stripTable.Rows[0][fd_stripWidth].ToString(), stripTable.Rows[0][fd_stripWeight].ToString(),
                    stripTable.Rows[0][fd_burrDirection].ToString(), stripTable.Rows[0][fd_status].ToString(), stripTable.Rows[0][fd_updateTime].ToString());

                string sqlINTFSTRIP = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')",
                    L2INTF_STRIP_INFO.tableName, eventNo, stripTable.Rows[0][fd_stripNo].ToString(), stripTable.Rows[0][fd_batchNoE].ToString(), stripTable.Rows[0][fd_taskNo].ToString(),
                    stripTable.Rows[0][fd_isSpy].ToString(), stripTable.Rows[0][fd_isEx].ToString(), stripTable.Rows[0][fd_stripDurabilityClass].ToString(), stripTable.Rows[0][fd_stripSteelGrade].ToString(),
                    stripTable.Rows[0][fd_stripHeat].ToString(), stripTable.Rows[0][fd_stripThk].ToString(), stripTable.Rows[0][fd_stripWidth].ToString(), stripTable.Rows[0][fd_stripWeight].ToString(),
                    stripTable.Rows[0][fd_burrDirection].ToString(), stripTable.Rows[0][fd_status].ToString(), stripTable.Rows[0][fd_updateTime].ToString());

                //string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                //    L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.insertNewStrip, DateTime.Now.ToString(Formats.GetDateFormat()), "1", userNo, "");

                //need not inform L3, only recored in L2 2022-03-25
                string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                    L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.insertNewStrip, DateTime.Now.ToString(Formats.GetDateFormat()), "2", userNo, "");

                List<string> sqlList = new List<string>();
                sqlList.Add(sqlSTRIP);
                sqlList.Add(sqlINTFSTRIP);
                sqlList.Add(sqlINTFEVENT);
                OracleHelper.ExecuteSqlTran(sqlList);
                LogHelper.WriteLog("Insert_STRIP_INFO Success");
                //新增strip后判断Task状态并改变
                TASK_INFO.UpdateTaskStatus(stripTable.Rows[0][fd_stripNo].ToString());
                return SelectAfterNewStripBytaskNoAndIndex(stripTable.Rows[0][fd_taskNo].ToString(), out totalPages);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Insert_STRIP_INFO", ex);
                throw ex;
            }
        }
        /// <summary>
        /// edit strip information
        /// </summary>
        /// <param name="stripTable"></param>
        /// <param name="userNo"></param>
        /// <returns></returns>
        public static DataTable UpdateStripByStripNo(DataTable stripTable, string userNo)
        {
            try
            {
                long eventNo = OracleHelper.GetNextEventNo();
                string sqlSTRIP = string.Format("UPDATE {0} SET {1}={2}, {3}='{4}', {5}='{6}',{7}='{8}',{9}='{10}',{11}='{12}',{13}='{14}',{15}='{16}',{17}='{18}',{19}='{20}'" +
                            ",{21}='{22}',{23}='{24}',{25}='{26}',{27}='{28}', {29}='{30}' where {31}='{32}'",
                               tableName, fd_eventNo, eventNo, fd_batchNoE, stripTable.Rows[0][fd_batchNoE].ToString(), fd_batchNoO, stripTable.Rows[0][fd_batchNoO].ToString(),
                               fd_taskNo, stripTable.Rows[0][fd_taskNo].ToString(), fd_isSpy, stripTable.Rows[0][fd_isSpy].ToString(), fd_isEx, stripTable.Rows[0][fd_isEx].ToString(), fd_stripDurabilityClass, stripTable.Rows[0][fd_stripDurabilityClass].ToString(), fd_stripSteelGrade, stripTable.Rows[0][fd_stripSteelGrade].ToString(),
                               fd_stripHeat, stripTable.Rows[0][fd_stripHeat].ToString(), fd_stripThk, stripTable.Rows[0][fd_stripThk].ToString(), fd_stripWidth, stripTable.Rows[0][fd_stripWidth].ToString(), fd_stripWeight, stripTable.Rows[0][fd_stripWeight].ToString(), fd_burrDirection, stripTable.Rows[0][fd_burrDirection].ToString(), fd_status, stripTable.Rows[0][fd_status].ToString(), fd_updateTime, stripTable.Rows[0][fd_updateTime].ToString(), fd_stripNo, stripTable.Rows[0][fd_stripNo].ToString());

                string sqlINTFSTRIP = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}')",
                    L2INTF_STRIP_INFO.tableName, eventNo, stripTable.Rows[0][fd_stripNo].ToString(), stripTable.Rows[0][fd_batchNoE].ToString(), stripTable.Rows[0][fd_taskNo].ToString(),
                    stripTable.Rows[0][fd_isSpy].ToString(), stripTable.Rows[0][fd_isEx].ToString(), stripTable.Rows[0][fd_stripDurabilityClass].ToString(), stripTable.Rows[0][fd_stripSteelGrade].ToString(),
                    stripTable.Rows[0][fd_stripHeat].ToString(), stripTable.Rows[0][fd_stripThk].ToString(), stripTable.Rows[0][fd_stripWidth].ToString(), stripTable.Rows[0][fd_stripWeight].ToString(),
                    stripTable.Rows[0][fd_burrDirection].ToString(), stripTable.Rows[0][fd_status].ToString(), stripTable.Rows[0][fd_updateTime].ToString());
                string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                    L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.updateStripStatus, DateTime.Now.ToString(Formats.GetDateFormat()), "1", userNo, "");
                List<string> sqlList = new List<string>();
                sqlList.Add(sqlSTRIP);
                sqlList.Add(sqlINTFSTRIP);
                sqlList.Add(sqlINTFEVENT);
                OracleHelper.ExecuteSqlTran(sqlList);
                LogHelper.WriteLog("UpdateStripByStripNo Success");

                return SelectAfterNewStripBytaskNoAndIndex(stripTable.Rows[0][fd_taskNo].ToString()); ;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdateStripByStripNo", ex);
                throw ex;
            }
        }
        /// <summary>
        /// delete strip by stripno
        /// </summary>
        /// <param name="StripNo"></param>
        /// <param name="userNo"></param>
        /// <returns></returns>
        public static DataTable DeleteStripInfoByStrip_code(string StripNo, string userNo, int minRow, int maxRow, out string totalPages)
        {
            try
            {
                long eventNo = OracleHelper.GetNextEventNo();
                string sqlStrip = string.Format("select * from {0} where {1}='{2}'",
                    tableName, fd_stripNo, StripNo);
                DataTable stripTable = OracleHelper.Exct_QueryTable(sqlStrip);

                if (stripTable != null && stripTable.Rows.Count > 0)
                {
                    string taskNo = stripTable.Rows[0][fd_taskNo].ToString();
                    string deleteStripSql = string.Format("delete from {0} where {1}='{2}'",
                      tableName, fd_stripNo, StripNo);
                    string sqlINTFTASKDELETE = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}')",
                        L2INTF_STRIP_DELETE.tableName, eventNo, StripNo, stripTable.Rows[0][fd_batchNoE].ToString(), stripTable.Rows[0][fd_taskNo].ToString(), DateTime.Now.ToString(Formats.GetDateFormat())
                        );
                    string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                        L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.deleteStrip, DateTime.Now.ToString(Formats.GetDateFormat()), "1", userNo, "");
                    List<string> sqlList = new List<string>();
                    sqlList.Add(deleteStripSql);
                    sqlList.Add(sqlINTFTASKDELETE);
                    sqlList.Add(sqlINTFEVENT);
                    OracleHelper.ExecuteSqlTran(sqlList);
                    LogHelper.WriteLog("DeleteStripInfoByStrip_code Success");
                    //删除strip后判断Task状态并改变
                    TASK_INFO.UpdateTaskStatusByTaskNo(taskNo);
                    return Select_STRIP_INFOByTaskNo(stripTable.Rows[0][fd_taskNo].ToString(), minRow, maxRow, out totalPages);
                }
                else
                {
                    totalPages = "";
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("DeleteStripInfoByStrip_code", ex);
                throw ex;
            }
        }
        /// <summary>
        /// After adding a strip, page out all the strips under taskNo
        /// </summary>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="recipeTotalPages"></param>
        /// <returns></returns>
        public static DataTable SelectAfterNewStripBytaskNoAndIndex(string taskNo, out string TotalPages)
        {
            try
            {
                string sqlCount = string.Format("select count(*) from {0} where {1}='{2}'",
                  tableName, fd_taskNo, taskNo);
                int Pages = Convert.ToInt32(TASK_INFO.GetTableCount(sqlCount));
                if (Pages % pageNum == 0)
                {
                    TotalPages = (Pages / pageNum).ToString();
                }
                else
                {
                    TotalPages = (Pages / pageNum + 1).ToString();
                }
                int minRow = 0;
                int maxRow = pageNum;
                string sqlStrip = string.Format("select * from (select ROWNUM rn,a.* from (select * from {0} where {1}='{2}' order by {3} asc,{4} desc) a) where rn>{5} and rn<={6}",
                  tableName, fd_taskNo, taskNo, fd_status, fd_updateTime, minRow, maxRow);
                DataTable stripTable = OracleHelper.Exct_QueryTable(sqlStrip);
                if (stripTable != null)
                {
                    LogHelper.WriteLog("SelectStripBytaskNoAndIndex Success");
                    return stripTable;
                }
                else
                {
                    TotalPages = "";
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("SelectStripBytaskNoAndIndex", ex);
                throw ex;
            }
        }
        /// <summary>
        /// query strip of the certain page by taskno
        /// </summary>
        /// <param name="taskNo"></param>
        /// <returns></returns>
        public static DataTable SelectAfterNewStripBytaskNoAndIndex(string taskNo)
        {
            try
            {
                int minRow = 0;
                int maxRow = pageNum;
                string sqlStrip = string.Format("select * from (select ROWNUM rn,a.* from (select * from {0} where {1}='{2}' order by {3} asc,{4} desc) a) where rn>{5} and rn<={6}",
                  tableName, fd_taskNo, taskNo, fd_status, fd_updateTime, minRow, maxRow);
                DataTable stripTable = OracleHelper.Exct_QueryTable(sqlStrip);
                if (stripTable != null)
                {
                    LogHelper.WriteLog("SelectStripBytaskNoAndIndex Success");
                    return stripTable;
                }
                else
                {
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("SelectStripBytaskNoAndIndex", ex);
                throw ex;
            }
        }
        /// <summary>
        /// fuzzy query strip of the certain page by stripNo
        /// </summary>
        /// <param name="stripNo"></param>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="TotalPages"></param>
        /// <returns></returns>
        public static DataTable QueryStripStripByStripNo(string stripNo, int minRow, int maxRow, out string TotalPages)
        {
            try
            {
                string sql = string.Format("select * from (select ROWNUM rn ,a.* from {0} a  where {1} like '%{2}%' order by {3} desc,{4} desc) where rn>{5} and rn<={6}",
                  tableName, fd_stripNo, stripNo, fd_updateTime, fd_stripNo, minRow, maxRow);
                string sqlCount = string.Format("select count(*) from (select * from {0} where {1} like '%{2}%')",
                   tableName, fd_stripNo, stripNo);
                DataTable table = OracleHelper.Exct_QueryTable(sql);
                if (table != null)
                {
                    int stripCounts = Convert.ToInt32(TASK_INFO.GetTableCount(sqlCount));
                    if (stripCounts % pageNum == 0)
                    {
                        TotalPages = (stripCounts / pageNum).ToString();
                    }
                    else
                    {
                        TotalPages = (stripCounts / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("QueryStripByStripNoByIndex Success");
                    return table;
                }
                else
                {
                    TotalPages = "";
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("QueryStripByStripNoByIndex", ex);
                throw ex;
            }
        }
    }
}
